package server.database;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.ZoneOffset;

import com.sun.beans.decoder.DocumentHandler;
import org.json.JSONObject;
import server.entity.*;



import org.json.JSONArray;


public class CommentDatabase{

    private static CommentDatabase dbInstance = null;


    public static synchronized CommentDatabase getInstance() {
        if (null == dbInstance) {
            dbInstance = new CommentDatabase();
        }
        return dbInstance;
    }

    public CommentDatabase(){

        try {
            if(ConnectionMyDataBase.dbConnection==null){
                ConnectionMyDataBase.makeConnection();
            }
            init();
        } catch (SQLException e) {
            System.out.println("Log - SQLexception");
        }

    }

    private boolean init() throws SQLException{

        if (ConnectionMyDataBase.dbConnection != null) {
            //创建表
            String createBasicDB = "CREATE TABLE IF NOT EXISTS comment (\n" +
                    "id integer PRIMARY KEY,\n"+
                    "time  integer,\n" +
                    "comment  varchar(50),\n" +
                    "associated_coord_id integer \n" +
                    ");";

//            String sql = "CREATE TABLE IF NOT EXISTS employees (\n" + " id integer PRIMARY KEY,\n"
//                    + " name text NOT NULL,\n" + " capacity real\n" + ");";
            Statement createStatement = ConnectionMyDataBase.dbConnection.createStatement();
            createStatement.executeUpdate(createBasicDB);
            createStatement.close();



            //Create database if no avaiable
            return true;
        }

        System.out.println("DB creation failed");
        return false;

    }


    public void storecomments(Comment comment,String  coord_id) throws SQLException{

        //Code to store comments to database
        String setMessageString = "insert into comment(time,comment,associated_coord_id) VALUES(?,?,?) ";
        System.out.println(setMessageString);

        PreparedStatement pstmt = ConnectionMyDataBase.dbConnection.prepareStatement(setMessageString);

        pstmt.setLong(1, comment.getTimeAsLong());
        pstmt.setString(2, comment.getComment());
        pstmt.setInt(3, Integer.parseInt(coord_id));
        pstmt.executeUpdate();
        System.out.println("Insert comment successfully");

    }


}